import express from 'express';
import { executeQuery, getOne } from '../config/database.js';
import { authenticateToken, requireAdmin } from '../middleware/auth.js';

const router = express.Router();

// 获取仪表盘统计数据（管理员）
router.get('/statistics', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;

    // 总体统计
    const overallStats = await getOne(`
      SELECT 
        COUNT(DISTINCT s.id) as total_students,
        COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) as present_count,
        COUNT(CASE WHEN ar.arrival_status = 'leave' AND ar.attendance_date = ? THEN 1 END) as leave_count,
        COUNT(CASE WHEN ar.arrival_status = 'abnormal' AND ar.attendance_date = ? THEN 1 END) as abnormal_count,
        ROUND(
          COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) * 100.0 / 
          NULLIF(COUNT(DISTINCT s.id), 0), 2
        ) as attendance_rate
      FROM students s
      LEFT JOIN attendance_records ar ON s.id = ar.student_id
    `, [date, date, date, date]);

    // 各班级出勤率
    const classStats = await executeQuery(`
      SELECT 
        c.id,
        c.name as class_name,
        c.grade,
        COUNT(DISTINCT s.id) as total_students,
        COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) as present_count,
        COUNT(CASE WHEN ar.arrival_status = 'leave' AND ar.attendance_date = ? THEN 1 END) as leave_count,
        COUNT(CASE WHEN ar.arrival_status = 'abnormal' AND ar.attendance_date = ? THEN 1 END) as abnormal_count,
        ROUND(
          COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) * 100.0 / 
          NULLIF(COUNT(DISTINCT s.id), 0), 2
        ) as attendance_rate,
        COALESCE(GROUP_CONCAT(DISTINCT u.name SEPARATOR ', '), '未分配') as head_teacher
      FROM classes c
      LEFT JOIN students s ON c.id = s.class_id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id
      LEFT JOIN teacher_classes tc ON c.id = tc.class_id
      LEFT JOIN users u ON tc.teacher_id = u.id AND u.role = 'teacher'
      GROUP BY c.id, c.name, c.grade
      ORDER BY c.grade, c.name
    `, [date, date, date, date]);

    // 入园异常学生
    const arrivalAbnormal = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name,
        c.name as class_name, c.grade,
        TIME(ar.arrival_timestamp) as arrival_time, ar.notes
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      INNER JOIN attendance_records ar ON s.id = ar.student_id
      WHERE ar.attendance_date = ? AND ar.arrival_status = 'abnormal'
      ORDER BY ar.arrival_timestamp DESC
    `, [date]);

    // 离园异常学生
    const departureAbnormal = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name,
        c.name as class_name, c.grade,
        TIME(ar.departure_timestamp) as departure_time, ar.notes
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      INNER JOIN attendance_records ar ON s.id = ar.student_id
      WHERE ar.attendance_date = ? AND ar.departure_status = 'abnormal'
      ORDER BY ar.departure_timestamp DESC
    `, [date]);

    // 校车统计
    const busStats = await getOne(`
      SELECT 
        COUNT(CASE WHEN ar.departure_status = 'bus' THEN 1 END) as bus_count,
        COUNT(CASE WHEN ar.departure_status = 'parent_pickup' THEN 1 END) as parent_pickup_count,
        COUNT(CASE WHEN ar.departure_status = 'after_school' THEN 1 END) as after_school_count,
        COUNT(CASE WHEN ar.departure_status = 'special_course' THEN 1 END) as special_course_count
      FROM attendance_records ar
      WHERE ar.attendance_date = ? AND ar.arrival_status = 'present'
    `, [date]);



    res.json({
      success: true,
      data: {
        date,
        overall: overallStats,
        classes: classStats,
        abnormal: {
          arrival: arrivalAbnormal,
          departure: departureAbnormal
        },
        departure_types: busStats
      }
    });

  } catch (error) {
    console.error('获取仪表盘统计错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取近期出勤趋势
router.get('/trends', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { days = 7 } = req.query;
    
    // 获取最近N天的出勤数据
    const trends = await executeQuery(`
      SELECT 
        ar.attendance_date,
        COUNT(DISTINCT ar.student_id) as total_students,
        COUNT(CASE WHEN ar.arrival_status = 'present' THEN 1 END) as present_count,
        COUNT(CASE WHEN ar.arrival_status = 'leave' THEN 1 END) as leave_count,
        COUNT(CASE WHEN ar.arrival_status = 'abnormal' THEN 1 END) as abnormal_count,
        ROUND(
          COUNT(CASE WHEN ar.arrival_status = 'present' THEN 1 END) * 100.0 / 
          NULLIF(COUNT(DISTINCT ar.student_id), 0), 2
        ) as attendance_rate
      FROM attendance_records ar
      WHERE ar.attendance_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
      GROUP BY ar.attendance_date
      ORDER BY ar.attendance_date DESC
    `, [parseInt(days)]);

    res.json({
      success: true,
      data: trends
    });

  } catch (error) {
    console.error('获取出勤趋势错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 生成班级出勤汇总报表（按日期分开统计）
router.get('/class-attendance-report', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { 
      startDate = new Date().toISOString().split('T')[0], 
      endDate = new Date().toISOString().split('T')[0] 
    } = req.query;

    // 生成日期范围内的所有日期
    const dates = [];
    const start = new Date(startDate);
    const end = new Date(endDate);
    
    for (let d = new Date(start); d <= end; d.setDate(d.getDate() + 1)) {
      dates.push(d.toISOString().split('T')[0]);
    }

    // 获取所有班级信息
    const classes = await executeQuery(`
      SELECT id, name, grade FROM classes ORDER BY grade, name
    `);

    // 按日期分开获取每日的班级出勤数据
    const dailyReports = [];
    
    for (const date of dates) {
      const classDataForDate = await executeQuery(`
        SELECT 
          c.id as class_id,
          c.name as class_name,
          c.grade,
          COUNT(DISTINCT s.id) as total_students,
          COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) as present_count,
          COUNT(CASE WHEN ar.arrival_status = 'leave' AND ar.attendance_date = ? THEN 1 END) as leave_count,
          COUNT(CASE WHEN ar.arrival_status = 'abnormal' AND ar.attendance_date = ? THEN 1 END) as abnormal_count,
          COUNT(DISTINCT s.id) - COUNT(CASE WHEN ar.attendance_date = ? THEN 1 END) as not_arrived_count,
          ROUND(
            COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) * 100.0 / 
            NULLIF(COUNT(DISTINCT s.id), 0), 2
          ) as attendance_rate
        FROM classes c
        INNER JOIN students s ON c.id = s.class_id
        LEFT JOIN attendance_records ar ON s.id = ar.student_id
        GROUP BY c.id, c.name, c.grade
        ORDER BY c.grade, c.name
      `, [date, date, date, date, date]);

      // 计算当日总体统计
      const dailySummary = {
        total_students: classDataForDate.reduce((sum, cls) => sum + cls.total_students, 0),
        present_count: classDataForDate.reduce((sum, cls) => sum + cls.present_count, 0),
        leave_count: classDataForDate.reduce((sum, cls) => sum + cls.leave_count, 0),
        abnormal_count: classDataForDate.reduce((sum, cls) => sum + cls.abnormal_count, 0),
        not_arrived_count: classDataForDate.reduce((sum, cls) => sum + cls.not_arrived_count, 0)
      };

      dailySummary.attendance_rate = dailySummary.total_students > 0 
        ? Math.round((dailySummary.present_count / dailySummary.total_students) * 100 * 100) / 100 
        : 0;

      dailyReports.push({
        date,
        summary: dailySummary,
        classes: classDataForDate
      });
    }

    res.json({
      success: true,
      data: {
        startDate,
        endDate,
        isDateRange: startDate !== endDate,
        dailyReports
      }
    });

  } catch (error) {
    console.error('生成班级出勤报表错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 生成日报表（保留原有功能，用于详细学生信息）
router.get('/daily-report', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;

    // 获取当日详细报表数据
    const reportData = await executeQuery(`
      SELECT 
        c.name as class_name,
        c.grade,
        s.student_no,
        s.name as student_name,
        CASE 
          WHEN ar.arrival_status IS NULL THEN '未到校'
          WHEN ar.arrival_status = 'present' THEN '正常入园'
          WHEN ar.arrival_status = 'leave' THEN '请假'
          WHEN ar.arrival_status = 'abnormal' THEN '异常'
        END as arrival_status_text,
        TIME(ar.arrival_timestamp) as arrival_time,
        CASE 
          WHEN ar.departure_status = 'not_left' OR ar.departure_status IS NULL THEN '未离园'
          WHEN ar.departure_status = 'bus' THEN '校车接送'
          WHEN ar.departure_status = 'parent_pickup' THEN '家长接领'
          WHEN ar.departure_status = 'after_school' THEN '晚托班'
          WHEN ar.departure_status = 'special_course' THEN '特色课程'
          WHEN ar.departure_status = 'abnormal' THEN '异常离园'
        END as departure_status_text,
        TIME(ar.departure_timestamp) as departure_time,
        ar.notes
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      ORDER BY c.grade, c.name, s.student_no
    `, [date]);

    // 生成汇总信息
    const summary = {
      total_students: reportData.length,
      present_count: reportData.filter(r => r.arrival_status_text === '正常入园').length,
      leave_count: reportData.filter(r => r.arrival_status_text === '请假').length,
      abnormal_count: reportData.filter(r => r.arrival_status_text === '异常').length,
      not_arrived_count: reportData.filter(r => r.arrival_status_text === '未到校').length,
      bus_count: reportData.filter(r => r.departure_status_text === '校车接送').length,
      parent_pickup_count: reportData.filter(r => r.departure_status_text === '家长接领').length
    };

    summary.attendance_rate = summary.total_students > 0 
      ? Math.round((summary.present_count / summary.total_students) * 100 * 100) / 100 
      : 0;

    res.json({
      success: true,
      data: {
        date,
        summary,
        details: reportData
      }
    });

  } catch (error) {
    console.error('生成日报表错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取班级教师的仪表盘数据
router.get('/teacher-dashboard', authenticateToken, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;
    const teacherId = req.user.id;

    // 获取教师负责的班级
    const teacherClasses = await executeQuery(`
      SELECT c.id, c.name, c.grade
      FROM classes c
      INNER JOIN teacher_classes tc ON c.id = tc.class_id
      WHERE tc.teacher_id = ?
    `, [teacherId]);

    if (teacherClasses.length === 0) {
      return res.json({
        success: true,
        data: {
          classes: [],
          summary: {
            total_students: 0,
            present_count: 0,
            leave_count: 0,
            abnormal_count: 0,
            attendance_rate: 0
          }
        }
      });
    }

    const classIds = teacherClasses.map(c => c.id);

    // 获取各班级统计
    const classStats = await executeQuery(`
      SELECT 
        c.id,
        c.name as class_name,
        c.grade,
        COUNT(DISTINCT s.id) as total_students,
        COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) as present_count,
        COUNT(CASE WHEN ar.arrival_status = 'leave' AND ar.attendance_date = ? THEN 1 END) as leave_count,
        COUNT(CASE WHEN ar.arrival_status = 'abnormal' AND ar.attendance_date = ? THEN 1 END) as abnormal_count,
        ROUND(
          COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) * 100.0 / 
          NULLIF(COUNT(DISTINCT s.id), 0), 2
        ) as attendance_rate
      FROM classes c
      LEFT JOIN students s ON c.id = s.class_id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id
      WHERE c.id IN (${classIds.map(() => '?').join(',')})
      GROUP BY c.id, c.name, c.grade
      ORDER BY c.grade, c.name
    `, [date, date, date, date, ...classIds]);

    // 总体统计
    const totalStats = classStats.reduce((acc, cls) => {
      acc.total_students += cls.total_students;
      acc.present_count += cls.present_count;
      acc.leave_count += cls.leave_count;
      acc.abnormal_count += cls.abnormal_count;
      return acc;
    }, {
      total_students: 0,
      present_count: 0,
      leave_count: 0,
      abnormal_count: 0
    });

    totalStats.attendance_rate = totalStats.total_students > 0 
      ? Math.round((totalStats.present_count / totalStats.total_students) * 100 * 100) / 100 
      : 0;

    res.json({
      success: true,
      data: {
        date,
        classes: classStats,
        summary: totalStats
      }
    });

  } catch (error) {
    console.error('获取教师仪表盘错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取班级详细报表
router.get('/class-detail-report/:classId', authenticateToken, async (req, res) => {
  try {
    const { classId } = req.params;
    const { date = new Date().toISOString().split('T')[0] } = req.query;

    // 获取班级信息
    const classInfo = await getOne(`
      SELECT id, name, grade FROM classes WHERE id = ?
    `, [classId]);

    if (!classInfo) {
      return res.status(404).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 获取学生详细考勤信息
    const students = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name,
        ar.arrival_status,
        TIME(ar.arrival_timestamp) as arrival_time,
        ar.departure_status,
        TIME(ar.departure_timestamp) as departure_time,
        ar.notes
      FROM students s
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      WHERE s.class_id = ?
      ORDER BY s.student_no
    `, [date, classId]);

    res.json({
      success: true,
      data: {
        class: classInfo,
        date,
        students
      }
    });

  } catch (error) {
    console.error('获取班级详细报表错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

export default router;
